package com.kern.dao;

import com.kern.bean.DsConnection;
import com.kern.entity.XProduct;
import com.kern.util.JDBCUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by caoshi at 10:37 2021/7/30
 */
@Component
public class ProductDao {

    private static final Logger logger = LoggerFactory.getLogger(ProductDao.class);

    @Autowired
    private DsConnection conn;


    public List<XProduct> findAll() {
        List<XProduct> productList = new ArrayList<>();

        Connection connection = null;
        PreparedStatement stat = null;
        try {
            connection = JDBCUtil.getConnection(conn);
            String sql = "select * from lj_product";
            stat = connection.prepareStatement(sql);
            ResultSet resultSet = stat.executeQuery();

            if (resultSet != null) {
                while (resultSet.next()) {
                    XProduct p = new XProduct();
                    p.setId(resultSet.getString("lj_product_id"));
                    p.setName(resultSet.getString("lj_product_name"));
                    p.setSubCategory(resultSet.getString("lj_product_subcategory"));
                    p.setCategory(resultSet.getString("lj_product_category"));
                    productList.add(p);
                }
                System.out.println(productList);
            }

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(connection, stat);
        }

        return productList;
    }

    public int batchInsert(List<XProduct> productList) {
        Connection connection = null;
        PreparedStatement stat = null;
        int size = productList.size();
        int successCount = 0;   // 插入成功的条数

        try {
            connection = JDBCUtil.getConnection(conn);
            // 关闭自动提交
            connection.setAutoCommit(false);

            String sql = "insert into lj_product(`lj_product_id`, `lj_product_name`, `lj_product_subcategory`, `lj_product_category`) values (?,?,?,?)";
            stat = connection.prepareStatement(sql);
            logger.debug("开始插入数据");
            long startTime = System.currentTimeMillis();
            for (XProduct xProduct : productList) {
                stat.setString(1, xProduct.getId());
                stat.setString(2, xProduct.getName());
                stat.setString(3, xProduct.getSubCategory());
                stat.setString(4, xProduct.getCategory());
                stat.addBatch();
            }

            stat.executeBatch();

            long endTime = System.currentTimeMillis();

            logger.info(">>>>>>>>>>>>>>>> 插入{}条数据用时: {} ms", size, endTime - startTime);

            String querySql = "select count(1) from lj_product";

            ResultSet resultSet = stat.executeQuery(querySql);
            while (resultSet.next()) {
                successCount = resultSet.getInt(1);
            }
            connection.commit();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(connection, stat);
        }

        return successCount;
    }


}
